﻿using Microsoft.Win32;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Net.Sockets;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Configuration;
using System.Data;
using System.IO;




namespace AlgoLogicMessenger
{
    /// <summary>
    /// Interaction logic for ManageQuizQuestion.xaml
    /// </summary>
    public partial class ManageQuizQuestion : Page
    {
        string connstr = @"Data Source=.;Initial Catalog=AlgoLogicDB;Integrated Security=True";

        public class DataItem
        {
            public string qnum { get; set; }
            public string qns { get; set; }
            public string fn { get; set; }
            public string answer { get; set; }
            public string picpath { get; set; }
            public string a { get; set; }
            public string b { get; set; }
            public string c { get; set; }
            public string d { get; set; }
            public string m { get; set; }
            public string s { get; set; }
            public string cat { get; set; }

            public DataItem(System.Data.DataRow r)
            {
                qnum = r[0].ToString();
                qns = r[1].ToString();
                cat = r[2].ToString();
                fn = r[3].ToString();
                answer = r[4].ToString();
                picpath = r[5].ToString();
                a = r[6].ToString();
                b = r[7].ToString();
                c = r[8].ToString();
                d = r[9].ToString();
                m = r[10].ToString();
                s = r[11].ToString();
            }
        }

        public class ExportItem
        {
            public string qns { get; set; }
            public string cat { get; set; }
            public string answer { get; set; }
            public string picpath { get; set; }
            public string a { get; set; }
            public string b { get; set; }
            public string c { get; set; }
            public string d { get; set; }
            public string m { get; set; }
            public string s { get; set; }

            public ExportItem(System.Data.DataRow r)
            {
                qns = r[1].ToString();
                cat = r[2].ToString();
                answer = r[4].ToString();
                picpath = r[5].ToString();
                a = r[6].ToString();
                b = r[7].ToString();
                c = r[8].ToString();
                d = r[9].ToString();
                m = r[10].ToString();
                s = r[11].ToString();
            }
        }

        public ManageQuizQuestion()
        {
            InitializeComponent();
            DisplayQuizQuestion();
            DisplayCategory();

            for (int i = 0; i <= 5; i++)
            {
                comboMin.Items.Add(i);
            }

            for (int i = 0; i <= 59; i++)
            {
                comboSec.Items.Add(i);
            }
        }

        private void DisplayQuizQuestion()
        {
            try
            {
                SqlConnection conn = new SqlConnection(connstr);
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                //comm.CommandText = "SELECT FileName, QuizNum, Question, Answer, Image, OptA, OptB, OptC, OptD, Min, Sec, Category from Export";
                comm.CommandText = "SELECT QuizNum, Question, Category, FileName, Answer, Image, OptA, OptB, OptC, OptD, Min, Sec from Export";
                comm.ExecuteNonQuery();

                SqlDataAdapter da = new SqlDataAdapter(comm);
                DataTable dt = new DataTable("Export"); // to create the Export table
                da.Fill(dt); // pass in the values exported from DB into table
                dataGrid1.ItemsSource = dt.DefaultView; //transfer table to Datagridview
                da.Update(dt);
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Unable to retrieve questions");
            }
        }

        private void DisplayCategory()
        {
            try
            {
                SqlConnection conn = new SqlConnection(connstr);
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT distinct Category from Export";
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    comboCategory.Items.Add(dr[0]);
                }
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }
        }

        private void ClearFields()
        {
            tbxQn.Text = "";
            tbxQnImage.Text = "";
            tbxOptAImage.Text = "";
            tbxOptBImage.Text = "";
            tbxOptCImage.Text = "";
            tbxOptDImage.Text = "";
            comboBoxAnswer.Text = "";
            comboMin.Text = "";
            comboSec.Text = "";
            comboCategory.Text = "";
        }

        private void btnUpload_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                OpenFileDialog FileDialog = new OpenFileDialog();
                FileDialog.Title = "Select an Image";
                FileDialog.InitialDirectory = "";
                FileDialog.Filter = "Image Files (*.gif,*.jpg,*.jpeg,*.bmp,*.png)|*.gif;*.jpg;*.jpeg;*.bmp;*.png";
                FileDialog.FilterIndex = 1;

                FileDialog.RestoreDirectory = true;

                FileDialog.ReadOnlyChecked = true;
                FileDialog.ShowReadOnly = true;

                if (FileDialog.ShowDialog() != null)
                {

                    tbxQnImage.Text = Path.GetFileName(FileDialog.FileName);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private void btnOptA_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                OpenFileDialog FileDialog = new OpenFileDialog();
                FileDialog.Title = "Select an Image";
                FileDialog.InitialDirectory = "";
                FileDialog.Filter = "Image Files (*.gif,*.jpg,*.jpeg,*.bmp,*.png)|*.gif;*.jpg;*.jpeg;*.bmp;*.png";
                FileDialog.FilterIndex = 1;

                FileDialog.RestoreDirectory = true;

                FileDialog.ReadOnlyChecked = true;
                FileDialog.ShowReadOnly = true;

                if (FileDialog.ShowDialog() != null)
                {

                    tbxOptAImage.Text = Path.GetFileName(FileDialog.FileName);
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private void btnOptB_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                OpenFileDialog FileDialog = new OpenFileDialog();
                FileDialog.Title = "Select an Image";
                FileDialog.InitialDirectory = "";
                FileDialog.Filter = "Image Files (*.gif,*.jpg,*.jpeg,*.bmp,*.png)|*.gif;*.jpg;*.jpeg;*.bmp;*.png";
                FileDialog.FilterIndex = 1;

                FileDialog.RestoreDirectory = true;

                FileDialog.ReadOnlyChecked = true;
                FileDialog.ShowReadOnly = true;

                if (FileDialog.ShowDialog() != null)
                {

                    tbxOptBImage.Text = Path.GetFileName(FileDialog.FileName);
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        private void btnOptC_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                OpenFileDialog FileDialog = new OpenFileDialog();
                FileDialog.Title = "Select an Image";
                FileDialog.InitialDirectory = "";
                FileDialog.Filter = "Image Files (*.gif,*.jpg,*.jpeg,*.bmp,*.png)|*.gif;*.jpg;*.jpeg;*.bmp;*.png";
                FileDialog.FilterIndex = 1;

                FileDialog.RestoreDirectory = true;

                FileDialog.ReadOnlyChecked = true;
                FileDialog.ShowReadOnly = true;

                if (FileDialog.ShowDialog() != null)
                {

                    tbxOptCImage.Text = Path.GetFileName(FileDialog.FileName);
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private void btnOptD_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                OpenFileDialog FileDialog = new OpenFileDialog();
                FileDialog.Title = "Select an Image";
                FileDialog.InitialDirectory = "";
                FileDialog.Filter = "Image Files (*.gif,*.jpg,*.jpeg,*.bmp,*.png)|*.gif;*.jpg;*.jpeg;*.bmp;*.png";
                FileDialog.FilterIndex = 1;

                FileDialog.RestoreDirectory = true;

                FileDialog.ReadOnlyChecked = true;
                FileDialog.ShowReadOnly = true;

                if (FileDialog.ShowDialog() != null)
                {

                    tbxOptDImage.Text = Path.GetFileName(FileDialog.FileName);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private void btnBack_Click(object sender, RoutedEventArgs e)
        {
            NavigationService nav = NavigationService.GetNavigationService(this);
            nav.Navigate(new System.Uri("ManageQuizModule.xaml", UriKind.RelativeOrAbsolute));
        }

        private void dataGrid1_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            if (dataGrid1.SelectedItem != null)
            {
                object selectedRow = dataGrid1.SelectedItem;
                string question = (dataGrid1.SelectedCells[1].Column.GetCellContent(selectedRow) as TextBlock).Text;

                SqlConnection conn = new SqlConnection(connstr);
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT QuizNum, Question, Category, FileName, Answer, Image, OptA, OptB, OptC, OptD, Min, Sec from Export where Question=@Question";
                comm.Parameters.AddWithValue("@Question", question);

                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    tbxQn.Text = dr["Question"].ToString();
                    comboCategory.Text = dr["Category"].ToString();
                    tbxQnImage.Text = dr["Image"].ToString();
                    tbxOptAImage.Text = dr["OptA"].ToString();
                    tbxOptBImage.Text = dr["OptB"].ToString();
                    tbxOptCImage.Text = dr["OptC"].ToString();
                    tbxOptDImage.Text = dr["OptD"].ToString();
                    comboBoxAnswer.Text = dr["Answer"].ToString();
                    comboMin.Text = dr["Min"].ToString();
                    comboSec.Text = dr["Sec"].ToString();

                }
                dr.Close();
                conn.Close();
            }
        }

        private void btnDelete_Click(object sender, RoutedEventArgs e)
        {
            if (dataGrid1.SelectedItems.Count != 0)
            {
                const string message = "Are you sure to delete question?";
                const string caption = "Deleting question";
                MessageBoxResult result = MessageBox.Show(message, caption, MessageBoxButton.YesNo, MessageBoxImage.Question);

                if (result == MessageBoxResult.Yes)
                {
                    List<DataItem> newQuestions = new List<DataItem>();
                    object selectedRow = dataGrid1.SelectedItem;

                    var row = ((System.Data.DataRowView)dataGrid1.SelectedItem).Row;

                    DataItem r = new DataItem(row);

                    foreach (object obj in dataGrid1.Items)
                    {
                        var dr = ((System.Data.DataRowView)obj).Row;
                        DataItem di = new DataItem(dr);
                        if (di.qnum != r.qnum)
                        {
                            newQuestions.Add(di);
                        }
                    }
                    try
                    {
                        SqlConnection conn = new SqlConnection(connstr);
                        conn.Open();
                        SqlCommand comm = new SqlCommand();
                        comm.Connection = conn;
                        comm.CommandText = "delete from Export DBCC CHECKIDENT (export, RESEED, 0)";
                        comm.ExecuteNonQuery();
                        conn.Close();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Unable to delete questions successfully");
                    }


                    foreach (DataItem di in newQuestions)
                    {
                        try
                        {
                            var conn = new SqlConnection(@"Data Source=localhost;Initial Catalog=AlgoLogicDB;Integrated Security=True");

                            conn.Open();
                            var comm = new SqlCommand();
                            comm.Connection = conn;
                            comm.CommandText = "insert into Export(Question, Category, FileName, Answer, Image, OptA, OptB, OptC, OptD, Min, Sec) " +
                            "values(@Question, @Category, @FileName, @Answer, @Image, @OptA, @OptB, @OptC, @OptD, @Min, @Sec)";
                            comm.Parameters.AddWithValue("@Question", di.qns);
                            comm.Parameters.AddWithValue("@Category", di.cat);
                            comm.Parameters.AddWithValue("@FileName", di.fn);
                            comm.Parameters.AddWithValue("@Answer", di.answer);
                            comm.Parameters.AddWithValue("@Image", di.picpath);
                            comm.Parameters.AddWithValue("@OptA", di.a);
                            comm.Parameters.AddWithValue("@OptB", di.b);
                            comm.Parameters.AddWithValue("@OptC", di.c);
                            comm.Parameters.AddWithValue("@OptD", di.d);
                            comm.Parameters.AddWithValue("@Min", di.m);
                            comm.Parameters.AddWithValue("@Sec", di.s);
                            comm.ExecuteNonQuery();
                            conn.Close();

                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show("Unable to establish connection to database");
                        }
                    }
                }
                MessageBox.Show("Question has been deleted successfully");
                dataGrid1.ItemsSource = null;
                DisplayQuizQuestion();
            }
            else
            {
                MessageBox.Show("Please select a question to delete.");
            }
        }

        private void BtnCreate_Click(object sender, RoutedEventArgs e)
        {
            if (createcombobx.SelectedItem == createlast)
            {
                const string message = "Are you sure to create question at the end of the questions?";
                const string caption = "Creating question";
                MessageBoxResult result = MessageBox.Show(message, caption, MessageBoxButton.YesNo, MessageBoxImage.Question);
                //first messagebox popup

                if (result == MessageBoxResult.Yes)
                {
                    SqlConnection conn = new SqlConnection(connstr);
                    conn.Open();
                    SqlCommand comm = new SqlCommand();
                    comm.Connection = conn;
                    comm.CommandText = "if exists(select * from AlgoLogicDB.dbo.Export where Question = @Question) select 'True'";
                    comm.Parameters.AddWithValue("@Question", tbxQn.Text);
                    SqlDataReader dr = comm.ExecuteReader();
                    if (dr.HasRows == true)
                    {
                        MessageBox.Show("Unable to create an existing question");
                        dr.Close();
                    }

                    else if (comboBoxAnswer.Text.Equals("") || tbxQn.Text.Equals("") || comboCategory.Text.Equals("") || tbxOptAImage.Text.Equals("") || tbxOptBImage.Text.Equals("") || tbxOptCImage.Text.Equals("") || tbxOptDImage.Text.Equals("") || comboMin.Text.Equals("") || comboSec.Text.Equals(""))
                    {
                        MessageBox.Show("Please fill up all the required blanks");
                        dr.Close();
                    }
                    else
                    {
                        comm.CommandText = "insert into Export(Question, Category, FileName, Answer, Image, OptA, OptB, OptC, OptD, Min, Sec)"
                        + "values(@Question1, @Category, @FileName, @Answer, @Image, @OptA, @OptB, @OptC, @OptD, @Min, @Sec)";
                        comm.Parameters.AddWithValue("@Question1", tbxQn.Text);
                        comm.Parameters.AddWithValue("@Category", comboCategory.Text);
                        comm.Parameters.AddWithValue("@FileName", "-");
                        comm.Parameters.AddWithValue("@Answer", comboBoxAnswer.Text);
                        if (tbxQnImage.Text == "")
                            comm.Parameters.AddWithValue("@Image", "-");
                        else
                            comm.Parameters.AddWithValue("@Image", tbxQnImage.Text);
                        comm.Parameters.AddWithValue("@OptA", tbxOptAImage.Text);
                        comm.Parameters.AddWithValue("@OptB", tbxOptBImage.Text);
                        comm.Parameters.AddWithValue("@OptC", tbxOptCImage.Text);
                        comm.Parameters.AddWithValue("@OptD", tbxOptDImage.Text);
                        comm.Parameters.AddWithValue("@Min", comboMin.Text);
                        comm.Parameters.AddWithValue("@Sec", comboSec.Text);
                        dr.Close();
                        comm.ExecuteNonQuery();
                        conn.Close();
                        MessageBox.Show("Question has been created at the end of the list succcessfully");

                        dataGrid1.ItemsSource = null; //clear datagrid 
                        DisplayQuizQuestion();

                        ClearFields();
                    }
                }
                else
                {
                    MessageBox.Show("Please select an option again.");
                }
            }
            else if (createcombobx.SelectedItem == createafter)
            {
                const string message1 = "Are you sure to create a question after the selected quiz question?";
                const string caption1 = "Creating question";
                MessageBoxResult result2 = MessageBox.Show(message1, caption1, MessageBoxButton.YesNo, MessageBoxImage.Question);
                //second messagebox to ask if the question wants to be create after the selected question
                if (result2 == MessageBoxResult.Yes)
                {
                    if (dataGrid1.SelectedItems.Count != 0)
                    {
                        object selectedRow = dataGrid1.SelectedItem;
                        string selectedcellcontent = (dataGrid1.SelectedCells[0].Column.GetCellContent(selectedRow) as TextBlock).Text;
                        int selectedquiznum = Convert.ToInt32(selectedcellcontent);
                        List<Question> storeItems = GetQuestions(selectedquiznum);//call the list
                        DeleteUnwantedQns(selectedquiznum);



                        SqlConnection conn5 = new SqlConnection(connstr);
                        conn5.Open();
                        SqlCommand comm5 = new SqlCommand();
                        comm5.Connection = conn5;
                        comm5.CommandText = "if exists(select * from AlgoLogicDB.dbo.Export where Question = @Question) select 'True'";
                        comm5.Parameters.AddWithValue("@Question", tbxQn.Text);
                        SqlDataReader dr = comm5.ExecuteReader();
                        if (dr.HasRows == true)
                        {
                            MessageBox.Show("Unable to create an existing question");
                            dr.Close();
                        }

                        else if (comboBoxAnswer.Text.Equals("") || tbxQn.Text.Equals("") || comboCategory.Text.Equals("") || tbxOptAImage.Text.Equals("") || tbxOptBImage.Text.Equals("") || tbxOptCImage.Text.Equals("") || tbxOptDImage.Text.Equals("") || comboMin.Text.Equals("") || comboSec.Text.Equals(""))
                        {
                            MessageBox.Show("Please fill up all the required blanks");
                            dr.Close();

                        }
                        else
                        {

                            try
                            {
                                comm5.CommandText = "insert into Export(Question, Category, FileName, Answer, Image, OptA, OptB, OptC, OptD, Min, Sec)"
                        + "values(@Question1, @Category, @FileName, @Answer, @Image, @OptA, @OptB, @OptC, @OptD, @Min, @Sec)";
                                comm5.Parameters.AddWithValue("@Question1", tbxQn.Text);
                                comm5.Parameters.AddWithValue("@Category", comboCategory.Text);
                                comm5.Parameters.AddWithValue("@FileName", "-");
                                comm5.Parameters.AddWithValue("@Answer", comboBoxAnswer.Text);
                                if (tbxQnImage.Text == "")
                                    comm5.Parameters.AddWithValue("@Image", "-");
                                else
                                    comm5.Parameters.AddWithValue("@Image", tbxQnImage.Text);
                                comm5.Parameters.AddWithValue("@OptA", tbxOptAImage.Text);
                                comm5.Parameters.AddWithValue("@OptB", tbxOptBImage.Text);
                                comm5.Parameters.AddWithValue("@OptC", tbxOptCImage.Text);
                                comm5.Parameters.AddWithValue("@OptD", tbxOptDImage.Text);
                                comm5.Parameters.AddWithValue("@Min", comboMin.Text);
                                comm5.Parameters.AddWithValue("@Sec", comboSec.Text);
                                dr.Close();
                                comm5.ExecuteNonQuery();
                                conn5.Close();
                            }
                            catch (SqlException ex)
                            {
                                MessageBox.Show(ex.Message);
                            }

                            finally
                            {
                                AddQBackToDB(storeItems);
                                dataGrid1.ItemsSource = null; //clear datagrid 
                                DisplayQuizQuestion();//retrieve from db
                                ClearFields(); //refresh
                                MessageBox.Show("Question has been created after the selected question succcessfully.");
                            }

                        }
                    }
                    else
                    {
                        MessageBox.Show("The new question will be created after a selected question. Please select a question.");

                    }
                }
                else
                {
                    MessageBox.Show("Please select an option again.");
                }
            }
            else if (createcombobx.SelectedItem == createfirst)
            {
                CreateFirst();
            }
            else
            {
                MessageBox.Show("Please select an option from the dropdownlist beside this button.");
            }

        }

        private void BtnSave_Click(object sender, RoutedEventArgs e)
        {
            const string message = "Are you sure to update question?";
            const string caption = "Updating question";
            MessageBoxResult result = MessageBox.Show(message, caption, MessageBoxButton.YesNo, MessageBoxImage.Question);

            if (result == MessageBoxResult.Yes)
            {
                if (dataGrid1.SelectedItem != null)
                {
                    object selectedRow = dataGrid1.SelectedItem;
                    string question = (dataGrid1.SelectedCells[1].Column.GetCellContent(selectedRow) as TextBlock).Text;

                    SqlConnection conn = new SqlConnection(connstr);
                    conn.Open();
                    SqlCommand comm = new SqlCommand();
                    comm.Connection = conn;

                    if (comboBoxAnswer.Text.Equals("") || tbxQn.Text.Equals("") || comboCategory.Text.Equals("") || tbxOptAImage.Text.Equals("") || tbxOptBImage.Text.Equals("") || tbxOptCImage.Text.Equals("") || tbxOptDImage.Text.Equals("") || comboMin.Text.Equals("") || comboSec.Text.Equals(""))
                    {
                        MessageBox.Show("Please fill up all the required blanks");
                    }

                    else
                    {
                        comm.CommandText = "update Export set Question=@Question2, Category=@Category, Answer=@Answer, Image=@Image, OptA=@OptA, OptB=@OptB, OptC=@OptC, OptD=@OptD, Min=@Min, Sec=@Sec where Question=@ExistingQuestion";
                        comm.Parameters.AddWithValue("@Question2", tbxQn.Text);
                        comm.Parameters.AddWithValue("@Category", comboCategory.Text);
                        comm.Parameters.AddWithValue("@Answer", comboBoxAnswer.Text);
                        if (tbxQnImage.Text == "")
                            comm.Parameters.AddWithValue("@Image", "-");
                        else
                            comm.Parameters.AddWithValue("@Image", tbxQnImage.Text);
                        comm.Parameters.AddWithValue("@OptA", tbxOptAImage.Text);
                        comm.Parameters.AddWithValue("@OptB", tbxOptBImage.Text);
                        comm.Parameters.AddWithValue("@OptC", tbxOptCImage.Text);
                        comm.Parameters.AddWithValue("@OptD", tbxOptDImage.Text);
                        comm.Parameters.AddWithValue("@Min", comboMin.Text);
                        comm.Parameters.AddWithValue("@Sec", comboSec.Text);
                        comm.Parameters.AddWithValue("@ExistingQuestion", question);
                        comm.ExecuteNonQuery();
                        conn.Close();
                        MessageBox.Show("Question has been updated succcessfully");
                        dataGrid1.ItemsSource = null;
                        DisplayQuizQuestion();
                    }
                }
                else
                {
                    MessageBox.Show("Please select a question");
                }
            }
        }

        private void btnClear_Click(object sender, RoutedEventArgs e)
        {
            ClearFields();
        }

        private void btnExport_Click(object sender, RoutedEventArgs e)
        {
            if (dataGrid1.Items.Count == 0)
            {
                MessageBox.Show("There are no questions to export.");
            }

            else
            {
                Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
                app.Visible = true;
                Microsoft.Office.Interop.Excel.Workbook workbook = app.Workbooks.Add(1);
                Microsoft.Office.Interop.Excel.Worksheet workSheet2 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1];
                workSheet2.Name = "Data";

                for (int j = 0; j < dataGrid1.Items.Count; j++)
                {
                    var row = ((System.Data.DataRowView)dataGrid1.Items[j]).Row;
                    ExportItem dv = new ExportItem(row);
                    string qns = dv.qns.ToString();
                    string cat = dv.cat.ToString();
                    string answer = dv.answer.ToString();
                    string picpath = dv.picpath.ToString();
                    string a = dv.a.ToString();
                    string b = dv.b.ToString();
                    string c = dv.c.ToString();
                    string d = dv.d.ToString();
                    string m = dv.m.ToString();
                    string s = dv.s.ToString();

                    workSheet2.Cells[1, 1] = "Question";
                    workSheet2.Cells[1, 2] = "Category";
                    workSheet2.Cells[1, 3] = "Answer";
                    workSheet2.Cells[1, 4] = "Image";
                    workSheet2.Cells[1, 5] = "OptA";
                    workSheet2.Cells[1, 6] = "OptB";
                    workSheet2.Cells[1, 7] = "OptC";
                    workSheet2.Cells[1, 8] = "OptD";
                    workSheet2.Cells[1, 9] = "Min";
                    workSheet2.Cells[1, 10] = "Sec";

                    workSheet2.Cells[j + 2, 1] = qns;
                    workSheet2.Cells[j + 2, 2] = cat;
                    workSheet2.Cells[j + 2, 3] = answer;
                    workSheet2.Cells[j + 2, 4] = picpath;
                    workSheet2.Cells[j + 2, 5] = a;
                    workSheet2.Cells[j + 2, 6] = b;
                    workSheet2.Cells[j + 2, 7] = c;
                    workSheet2.Cells[j + 2, 8] = d;
                    workSheet2.Cells[j + 2, 9] = m;
                    workSheet2.Cells[j + 2, 10] = s;
                }

                Microsoft.Office.Interop.Excel.Worksheet workSheet1 = workbook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing)
                                as Microsoft.Office.Interop.Excel.Worksheet;
                workSheet1.Name = "Input";
                workSheet2.Select(Type.Missing);
                app.Quit();
            }
        }

        private List<Question> GetQuestions(int QuizNum)
        {

            List<Question> queslist = new List<Question>();

            try
            {
                SqlConnection Conn = new SqlConnection(connstr);
                Conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = Conn;
                comm.CommandText = "Select Question, Category, FileName, Answer, Image, OptA, OptB, OptC, OptD, Min, Sec from Export where QuizNum>@SelectedQuizNum";
                comm.Parameters.AddWithValue("@SelectedQuizNum", QuizNum);
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    //call the question class
                    Question questions = new Question();
                    //saving part
                    questions.qns = dr.GetString(0);
                    questions.cat = dr.GetString(1);
                    questions.fn = dr.GetString(2);
                    questions.answer = dr.GetString(3);
                    questions.picpath = dr.GetString(4);
                    questions.a = dr.GetString(5);
                    questions.b = dr.GetString(6);
                    questions.c = dr.GetString(7);
                    questions.d = dr.GetString(8);
                    questions.m = dr.GetInt32(9);
                    questions.s = dr.GetInt32(10);


                    //if (list == null)
                    //{
                    //list = new List<Question>();
                    queslist.Add(questions);
                    //}
                }
                dr.Close();
                Conn.Close();

            }


            catch (Exception ex)
            {
                MessageBox.Show("Unable to save the values into the list.");
            }

            return queslist;
        }
        private List<Question> GetAllQuestions()
        {

            List<Question> fullqueslist = new List<Question>();

            try
            {
                SqlConnection Conn = new SqlConnection(connstr);
                Conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = Conn;
                comm.CommandText = "Select Question, Category, FileName, Answer, Image, OptA, OptB, OptC, OptD, Min, Sec from Export";
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    //call the question class
                    Question questions = new Question();
                    //saving part
                    questions.qns = dr.GetString(0);
                    questions.cat = dr.GetString(1);
                    questions.fn = dr.GetString(2);
                    questions.answer = dr.GetString(3);
                    questions.picpath = dr.GetString(4);
                    questions.a = dr.GetString(5);
                    questions.b = dr.GetString(6);
                    questions.c = dr.GetString(7);
                    questions.d = dr.GetString(8);
                    questions.m = dr.GetInt32(9);
                    questions.s = dr.GetInt32(10);


                    //if (list == null)
                    //{
                    //list = new List<Question>();
                    fullqueslist.Add(questions);
                    //}
                }
                dr.Close();
                Conn.Close();

            }


            catch (Exception ex)
            {
                MessageBox.Show("Unable to save the values into the list.");
            }

            return fullqueslist;
        }

        private void DeleteUnwantedQns(int SelectedQuizNum)
        {
            try
            {
                SqlConnection Conn = new SqlConnection(connstr);
                Conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = Conn;
                comm.CommandText = "Delete from Export where QuizNum>@SelectedQuizNum DBCC CHECKIDENT (export, RESEED, @SelectedQuizNum)";
                comm.Parameters.AddWithValue("@SelectedQuizNum", SelectedQuizNum);
                comm.ExecuteNonQuery();
                Conn.Close();

            }


            catch (Exception ex)
            {
                MessageBox.Show("Unable to delete questions after selected quiz number.");
            }

        }

        private void DeleteAllQns()
        {
            try
            {
                SqlConnection Conn = new SqlConnection(connstr);
                Conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = Conn;
                comm.CommandText = "Delete from Export DBCC CHECKIDENT (export, RESEED, 0)";
                comm.ExecuteNonQuery();
                Conn.Close();
            }


            catch (Exception ex)
            {
                MessageBox.Show("Unable to delete questions after selected quiz number.");
            }

        }

        private void CreateNewQns()
        {
            SqlConnection conn = new SqlConnection(connstr);
            conn.Open();
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = "if exists(select * from AlgoLogicDB.dbo.Export where Question = @Question) select 'True'";
            comm.Parameters.AddWithValue("@Question", tbxQn.Text);
            SqlDataReader dr = comm.ExecuteReader();
            if (dr.HasRows == true)
            {
                MessageBox.Show("Unable to create an existing question");
                dr.Close();
            }

            else if (comboBoxAnswer.Text.Equals("") || tbxQn.Text.Equals("") || comboCategory.Text.Equals("") || tbxOptAImage.Text.Equals("") || tbxOptBImage.Text.Equals("") || tbxOptCImage.Text.Equals("") || tbxOptDImage.Text.Equals("") || comboMin.Text.Equals("") || comboSec.Text.Equals(""))
            {
                MessageBox.Show("Please fill up all the required blanks");
                dr.Close();
            }
            else
            {
                comm.CommandText = "insert into Export(Question, Category, FileName, Answer, Image, OptA, OptB, OptC, OptD, Min, Sec)"
                + "values(@Question1, @Category, @FileName, @Answer, @Image, @OptA, @OptB, @OptC, @OptD, @Min, @Sec)";
                comm.Parameters.AddWithValue("@Question1", tbxQn.Text);
                comm.Parameters.AddWithValue("@Category", comboCategory.Text);
                comm.Parameters.AddWithValue("@FileName", "-");
                comm.Parameters.AddWithValue("@Answer", comboBoxAnswer.Text);
                if (tbxQnImage.Text == "")
                    comm.Parameters.AddWithValue("@Image", "-");
                else
                    comm.Parameters.AddWithValue("@Image", tbxQnImage.Text);
                comm.Parameters.AddWithValue("@OptA", tbxOptAImage.Text);
                comm.Parameters.AddWithValue("@OptB", tbxOptBImage.Text);
                comm.Parameters.AddWithValue("@OptC", tbxOptCImage.Text);
                comm.Parameters.AddWithValue("@OptD", tbxOptDImage.Text);
                comm.Parameters.AddWithValue("@Min", comboMin.Text);
                comm.Parameters.AddWithValue("@Sec", comboSec.Text);
                dr.Close();
                comm.ExecuteNonQuery();
                conn.Close();
            }
        }
        private void AddQBackToDB(List<Question> list)
        {
            if (list.Count != 0)
            {
                foreach (Question item in list)
                {
                    try
                    {
                        SqlConnection conn = new SqlConnection(connstr);
                        conn.Open();
                        SqlCommand comm = new SqlCommand();
                        comm.Connection = conn;

                        comm.CommandText = "insert into Export(Question, Category, FileName, Answer, Image, OptA, OptB, OptC, OptD, Min, Sec)"
                                + "values(@Question1, @Category, @FileName, @Answer, @Image, @OptA, @OptB, @OptC, @OptD, @Min, @Sec)";
                        comm.Parameters.AddWithValue("@Question1", item.qns);
                        comm.Parameters.AddWithValue("@Category", item.cat);
                        comm.Parameters.AddWithValue("@FileName", "-");
                        comm.Parameters.AddWithValue("@Answer", item.answer);
                        comm.Parameters.AddWithValue("@Image", item.picpath);
                        comm.Parameters.AddWithValue("@OptA", item.a);
                        comm.Parameters.AddWithValue("@OptB", item.b);
                        comm.Parameters.AddWithValue("@OptC", item.c);
                        comm.Parameters.AddWithValue("@OptD", item.d);
                        comm.Parameters.AddWithValue("@Min", item.m);
                        comm.Parameters.AddWithValue("@Sec", item.s);
                        comm.ExecuteNonQuery();
                        conn.Close();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Unable to insert new question into the database.");
                    }
                }

            }
        }

        private void CreateFirst()
        {
            //create at the front of the list
            const string message = "Are you sure to create a question at the beginning?";
            const string caption = "Creating question";
            MessageBoxResult result = MessageBox.Show(message, caption, MessageBoxButton.YesNo, MessageBoxImage.Question);
            //third messagebox popup
            if (result == MessageBoxResult.Yes)
            {
                List<Question> fullList = GetAllQuestions();//save all values from the db to the list
                DeleteAllQns();//delete all and reset the index
                CreateNewQns();//add new question into the db
                AddQBackToDB(fullList);//add back the list into the db          
                dataGrid1.ItemsSource = null; //clear datagrid 
                DisplayQuizQuestion();//retrieve from db
                ClearFields(); //refresh
                MessageBox.Show("Question has been created at the beginning of the questions.");
            }
            else
            {
                MessageBox.Show("Please select an option again.");
            }
        }

    }
}

